Fritz: All Fritz

home *** CD-ROM | disk | FTP | other *** search

/ Fritz: All Fritz / All Fritz.zip / All Fritz / FILES / SPREOTUS / 123PWRWK.LZH / NPV.DOC < prev next >

Wrap

Text File | 1984-09-09 | 20.4 KB | 529 lines

NPV - Net Present Value Calculator 1. INTRODUCTION The net present value of a cash flow (at a given discount rate) is found by discounting all values in the cash flow back to the opening period ('now') and calculating their sum, taking into account the sign of each value. It yields a 'current value' for the cash flow, allowing for the time value of money (ie. $1 now is worth more than $1 next year). Net present value is a commonly-used method of evaluating projects or investments, or of comparing two investments with different cash flow characteristics. (Another measure is the rate of return, usually calculated as the discount rate which reduces the net present value of the cash flow to zero. See the ROR worksheet for a discussion of rate of return.) This worksheet will calculate the net present value of a cash flow at a number of discount rates. You can produce a plot of the cash flow and cumulative cash flow by period. And you can produce a plot of the net present value against the discount rate. The discount rate at which this latter graph crosses the X axis is the internal rate of return of the cash flow (see the ROR worksheet). 2. USING NPV 2.1 Overview The main menu of the NPV worksheet offers you seven options (plus Quit). They are: Input This option allows you to enter cash flow values and discount rates. It presents a sub-menu from which to select the data you want to input. Blank This option blanks out the input and results areas of the worksheet, after receiving confirmation this is what you intend. It is useful for clearing out all data before entering an entirely new cash flow. Calc The calc option performs all calculations. You must use this option whenever you change data or enter new data. Results When you select this option, you are presented a sub menu allowing you to choose between the cash flow and the net present value areas. The only essential difference is in the set of column headings the program freezes as you scroll through the data. 09/09/84 NPV - 1 Graph This worksheet offers two main graphs. You can plot the cash flow and cumulative cash flow by period; and you can plot the net present values against the discount rates. Print This option sends the cash flow and net present value data to your printer, after a pause for paper alignment. Agenda If you select this option, and confirm it when requested, the current worksheet is lost and replaced by the main worksheet selection agenda. You can then load another worksheet. Note confirmation is required before this option proceeds, because it overwrites the worksheet in memory. 2.2 Input Details Choosing the Input option from the main menu leads to a sub menu with three options - Cashflow, Periods/Yr, and DiscRate. Each option allows you to enter part of the data. The Cashflow option allows you to enter cash flow values by period. This option places the cell pointer at the top of the cash flow column and moves it down one cell each time you press the Enter key. Thus, if you want to enter the entire cash flow, simply type each number in turn and press the Enter key after each one. Please note the following: - A value must be entered for each period covering the entire duration of the project and starting at period zero. If the cash flow value for a particular period is zero, enter zero. Do not leave the cell blank. - Period zero is now, period one is one period (year, month, or whatever) hence. - Cash outflows must be entered as negative numbers; cash inflows must be entered as positive numbers. If you don't want to enter the whole cash flow (for example, if you want to adjust a cash flow you've already entered) you may use the cursor movement keys to place the cell pointer on any number you want to change, and re-type it in normal 1-2-3 fashion. When you press the Enter key, the macro will move the cell pointer down one row. The macro which drives this process has no way of knowing when you've finished entering or changing data. When you have finished, you must break out of the macro and re-invoke the input menu. To do this, hold down the Ctrl key and press the Break key. The CTL flag at the top of the screen will go out, indicating you've broken out of the macro. Then hold down the Alt key and press I to invoke the input menu. 09/09/84 NPV - 2 The Periods/Yr option accepts the number of periods that correspond to a year. If you have annual data, you must enter 1. If you have monthly data, enter 12. This parameter is used to convert the discount rates from the annual values you enter to values applicable to periods. Thus, whatever the periods your data represents, you always enter discount rates as annual values. The third choice in the input menu is DiscRate. It allows you to enter annual discount rates for which the worksheet is to calculate the net present value. Enter the rates as percentages. Do not use the % key or convert to a decimal (for example, to enter 20%, type 20 and press the Enter key). It's a good idea to include zero in the set of discount rates, because this gives the straight sum of the cash flow values - a figure you can calculate independently to check your data entry. 2.3 Graph Details This worksheet can produce two different graphs on either the color or monochrome display. Each graph can take the form of a line graph, a bar chart, or a scatter graph (points without connecting lines). These are selected by means of a menu with four choices. The Cashflow option displays a graph of the cash flow by period. The form of the graph will be the same as the one that was last displayed. The NPV option displays a graph of the net present value against the discount rate. This shows you how the net present value behaves as future values are discounted more or less heavily. The discount rate (if any) at which the graph crosses the X-axis represents the rate which reduces the net present value of the cash flow to zero. This is known as the internal rate of return of the cash flow. The Type option presents a sub-menu from which you can select the form of the graph. The three choices are Line, Bar, and Scatter. Each choice will immediately re-display the last graph in the selected form, then return to the graph menu. The final choice - Options - allows you to choose the Color or monochrome (B&W) monitor for the display. Again, each choice re-displays the last graph on the selected device. 3. EXAMPLES 3.1 A Simple Investment This example shows how the net present value varies with discount rate when the cash flow is well-behaved. You have been given the opportunity to purchase a note for $15,000. The note will return $5,000 at the end of the first year, $7,000 at the end of the second year, and $9,000 at the end of the third year. What is the net present value of this investment at various discount rates? 09/09/84 NPV - 3 Keystroke Comments {ENTER} Select the Input option from the main menu. P 1 {ENTER} Select the Periods/Yr option from the input menu, and enter the number of periods per year (the data is annual) C Select the Cashflow option from the input menu. The cell pointer will go to the top of the cash flow column. -15000 {ENTER} Enter the value for period zero (now). Since this is a payment (ie. a cash outflow) its value is entered as a negative number. When you press the ENTER key, the cell pointer moves down to the next cell in the column. 5000 {ENTER} Enter the value returned at the end of the first year. This is a cash inflow. 7000 {ENTER} Enter the value returned at the end of the second year. 9000 {ENTER} Enter the value returned at the end of the third year. {Ctrl}{Break} To terminate the input loop, hold down the Ctrl key and press the Break key. The CMD flag at the top of the screen will go out. {Alt} I To re-invoke the input menu, hold down the Alt key and press I. D Select the DiscRate option to enter the discount rates, as annual percentages. The cell pointer will move to the top cell in the discount rate column. 0 {ENTER} The net present value at zero percent is the sum of the cash flow items. In this example, it's easy to check your input by eye, but you may want to get into the habit of always entering zero in the discount rate column to give you a total cash flow, which you could check independently. 5 {ENTER} Enter the remaining discount rates. 10 {ENTER} 15 {ENTER} 20 {ENTER} 25 {ENTER} 30 {ENTER} {Ctrl}{Break} As for the cash flow column, hold down the Ctrl key and press Break to terminate the input loop. 09/09/84 NPV - 4 {Alt} I Then hold down the Alt key and press I to re- invoke the input menu. Q Return to the main menu C Select the Calc option in the main menu. The net present values will be calculated G Select the Graph option from the main menu. text {ENTER} Whenever you enter the graph menu, the system will ask you to enter a second title line for the graphs. You may leave this line blank, or enter any short description. To get rid of an existing description before typing a new one, press the Esc key. N This option displays a graph of the net present value against the discount rates. Press any key to return to the graph menu. T L If the graph is not already a line graph, you can go to the Type sub-menu to specify a line graph. The graph will be re-displayed. Press any key to return to the graph menu. The line graph shows that the net present value of the cash flow is zero at a discount rate of about 17%. This is the internal rate of return of the cash flow. You should buy the note if this rate of return is sufficiently high, bearing in mind all other factors (such as security). 3.2 An Accelerated Income Project Many projects in the resources industry are aimed at accelerating resource recoveries. This leads to increased cash flows in early years, followed by compensating decreased cash flows in later years. Thus the cash flow consists of a series of outflows (the investment), followed by a series of inflows (the increased cash flows) followed by another series of outflows (the reduced cash flows). This is not a 'well-behaved' cash flow. The NPV worksheet can illustrate how the net present value of such a cash flow varies with discount rate. You are considering the value of an enhanced recovery project. The immediate cost of this project is $600. It will increase cash flows in the next three years by $800, $500, and $200 respectively. In the three years after that, however, cash flows will be reduced by $200, $500, and $800 respectively. You need to know the net present value of this project at various rates of return. 09/09/84 NPV - 5 Keystroke Comments {ENTER} Select the first option - Input - from the main menu. P 1 {ENTER} Enter the number of periods per year (the data is annual). C Select the Cashflow option from the input menu. The cell pointer will go to the top cell of the cash flow column. -600 {ENTER} Enter the initial investment (in period zero). The cell pointer will automatically move down to the next period when you press the Enter key. 800 {ENTER} Enter the increases in cash flow values in the 500 {ENTER} first three years. 200 {ENTER} -200 {ENTER} Enter the decreases in cash flow values in the -500 {ENTER} next three years. -800 {ENTER} {Ctrl}{Break} When all the cash flow values have been entered, hold down the Ctrl key and press the Break key to break out of the macro. Alt I To re-invoke the input menu, hold down the Alt key and press I. D Select the DiscRates option in the input menu to enter discount rates. The cell pointer will go to the top cell in the discount rates column. 0 {ENTER} Enter the discount rates, as annual percentages 5 {ENTER} Each time you press the Enter key, the cell 10 {ENTER} pointer will move down to the next cell in the 15 {ENTER} column. 20 {ENTER} 25 {ENTER} 30 {ENTER} 35 {ENTER} 40 {ENTER} 45 {ENTER} 50 {ENTER} 55 {ENTER} 60 {ENTER} 65 {ENTER} 70 {ENTER} 75 {ENTER} 80 {ENTER} 85 {ENTER} 90 {ENTER} 95 {ENTER} 100 {ENTER} 09/09/84 NPV - 6 {Ctrl}{Break} When you have entered all the discount rates, hold down the Ctrl key and press the Break key to break out of the input loop. {Alt} I To re-invoke the input menu, hold down the Alt key and type I. Q Return to the main menu. C Invoke the calculations. G Select the graph option from the main menu. text {ENTER} Each time you invoke the graph menu, it asks you to enter the second title line for the graphs. You may enter any short description. To remove an existing description before typing a new one, press the Esc key. N Select the NPV option in the graph menu to display a graph of the net present value against the annual discount rates. Press any key to return to the graph menu. T L Specify the type of graph as a line graph. The graph will immediately be re-displayed. Press any key to return to the graph menu. Q Return to the main menu Q Exit to 1-2-3 READY mode. To return to the main menu, hold down the Alt key and type M. 09/09/84 NPV - 7 Menu Tree for NPV Worksheet Input - Input periods/year, cash flow, and discount rates . Periods/Yr - Enter number of periods per year . Cashflow - Enter cash flow values by period . DiscRate - Enter annual discount rates (%) . Quit - Return to main menu Blank - Blank out all input areas . No - Do not erase data. Return to main menu . Yes - Erase all input data and results Calc - Calculate Results Results - Display Results . Cashflow - Display cashflow figures and column headings . . Down - Scroll down one screen . . Up - Scroll up one screen . . Quit - Return to results menu . NPV - Display net present values and column headings . . Down - Scroll down one screen . . Up - Scroll up one screen . . Quit - Return to results menu . Quit - Return to main menu Graph - Plot graphs of cashflow and/or results . Cashflow - Display graph of cashflow and cumulative cashflow . NPV - Display graph of net present values . Type - Set type of graph . . Line - Display a line graph . . Bar - Display a bar chart . . Scatter - Display a scatter chart . Options - Set color or monochrome display option . . Color - Display graph in color . . B&W - Display graph in monochrome . Quit - Return to main menu Print - Print Results Agenda - Return to Worksheet Selection Agenda . No - Do not leave this worksheet. Return to main menu . Yes - Leave this worksheet. Load the selection agenda Quit - Exit to 1-2-3 READY mode 09/09/84 NPV - 8